package cn.pn.project.mapper.base;

import cn.pn.project.pojo.base.City;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 定位
 */
@Mapper
public interface CityPositioningDao {
    /**
     * 通过城市名称查询城市信息
     * @return 全部城市集合
     */
    @Select("SELECT * FROM city WHERE cityName=#{cityName};")
    City findCityBycityName(@Param("cityName") String cityName) throws Exception;


    /**
     * 查询所有
     * @return
     * @throws Exception
     */
    @Select("SELECT * FROM  city")
    List<City> listCity() throws  Exception;

    /**
     * 查询销售量前10的城市
     * @return
     * @throws Exception
     */
    @Select("SELECT i.cityId,i.cityEnName,i.cityName,SUM(totalPrice) FROM pn_order.`mainorder` m" +
            " INNER JOIN pn_commodity.sku s ON m.skuId=s.skuId" +
            " INNER JOIN pn_commodity.commodity c ON s.commdityId=c.commodityId" +
            " INNER JOIN pn_base.city i ON c.cityId=i.cityId" +
            " WHERE c.provinceId!=0" +
            " Order BY(SUM(totalPrice)) DESC" +
            " limit 10")
    List<City> listCityTopTen() throws  Exception;
}
